class Character < ActiveRecord::Base
  validates_presence_of :christian_name
  validates_presence_of :family_name
  validates_numericality_of :honour
  validates_numericality_of :prestige
  validates_numericality_of :piety
  validates_numericality_of :wealth
  validates_numericality_of :diplomacy
  validates_numericality_of :martial
  validates_numericality_of :stewardship
  validates_numericality_of :intrigue
  validates_numericality_of :loyalty
  validates_numericality_of :fertility
  validates_numericality_of :health
  
  belongs_to  :player
  belongs_to  :culture
  has_many    :character_traits
  has_many    :titles
  has_many    :relations
  has_many    :estates
  
  def Character.find_unmarried(liege, sex = nil, min_age = 16)
    return nil unless liege
    territory = liege.court_territory
    return nil unless territory
    sql_select = Character.sql_select_from_character
    sql_where = " WHERE " + Character.sql_clause_realm(territory,true)
      
    if sex
      sql_where += " AND " + Character.sql_clause_sex (sex)
    end
    
    sql_where += " AND " + Character.sql_clause_alive_only
    sql_where += " AND " + Character.sql_clause_marital_status_excluded ("married")
    sql_where += " AND " + Character.sql_clause_family_name (liege.family_name)
    sql_where += " AND " + Character.sql_clause_exclude_clergy
    sql_where += " AND " + Character.sql_clause_min_age(min_age)
    
    Character.find_by_sql(sql_select + sql_where + Character.sql_order_by_oldest)
  end
  
  def Character.find_unmarried_for(character, liege = nil, min_age = 16)
    return nil unless character
    sql_select = Character.sql_select_from_character
    sql_where = " WHERE " + Character.sql_clause_sex (character.opposite_sex)
    sql_where += " AND " + Character.sql_clause_alive_only
    sql_where += " AND " + Character.sql_clause_marital_status_excluded ("married")
    sql_where += " AND " + Character.sql_clause_exclude_family_name (character.family_name)
    sql_where += " AND " + Character.sql_clause_exclude_clergy
    sql_where += " AND " + Character.sql_clause_min_age(min_age)
    
    if liege
      sql_where += " AND " + Character.sql_clause_family_name (liege.family_name)
      sql_where += " AND " + Character.sql_clause_vassal_of(liege)
    end
    
    puts "where='#{sql_where}''"
    
    Character.find_by_sql(sql_select + sql_where + Character.sql_order_by_oldest)
  end
  
  def Character.find_all_alive (sex = nil)
    sql_select = Character.sql_select_from_character
    sql_where = " WHERE " + Character.sql_clause_alive_only
    
    if sex
      sql_where += " AND " + Character.sql_clause_sex (sex)
    end

    Character.find_by_sql(sql_select + sql_where + Character.sql_order_by_oldest)
  end
  
  def Character.find_all_courtiers(territory, sex = nil, alive_only = true, marital_status_excluded = nil, family_name = nil, exclude_clergy = false, min_age = 0, exclude_family_name = nil)
    return nil unless territory
    sql_select = Character.sql_select_from_character
    sql_where = " WHERE " + Character.sql_clause_realm(territory,true)
    
    if sex
      sql_where += " AND " + Character.sql_clause_sex (sex)
    end
    
    if alive_only
      sql_where += " AND " + Character.sql_clause_alive_only
    end
    
    if marital_status
      sql_where += " AND " + Character.sql_clause_marital_status_excluded (marital_status_excluded)
    end
    
    if family_name
      sql_where += " AND " + Character.sql_clause_family_name (family_name)
    end
    
    if exclude_family_name
      sql_where += " AND " + Character.sql_clause_exclude_family_name (family_name)
    end
    
    if exclude_clergy
      sql_where += " AND " + Character.sql_clause_exclude_clergy
    end
    
    if min_age
      sql_where += " AND " + Character.sql_clause_min_age(min_age)
    end

    Character.find_by_sql(sql_select + sql_where + Character.sql_order_by_oldest)
  end
  
  def Character.find_all_npcs_by_realm_and_title_and_sex(territory = nil, title_category = nil, title_sub_category = nil, sex = nil, alive_only = true)
    sql_select = Character.sql_select_from_character
    sql_where = " WHERE " + Character.sql_clause_npc_only
    
    if sex
      sql_where += " AND " + Character.sql_clause_sex (sex)
    end

    if title_category && title_sub_category && title_category.to_i > 0 && title_sub_category.to_i > 0
      sql_where += " AND " + Character.sql_clause_title(title_category, title_sub_category)
    end
    
    if territory
      sql_where += " AND " + Character.sql_clause_realm(territory)
    end
    
    if alive_only
      sql_where += " AND " + Character.sql_clause_alive_only
    end
    Character.find_by_sql(sql_select + sql_where + Character.sql_order_by_oldest)
  end
  
  def Character.sexes
    [
    "male",
    "female"
    ]
  end
  
  def Character.marital_status
    [
    "single",
    "bethrothed",
    "married",
    "widowed"
    ]
  end
  
  def Character.born(father, mother, sex = rand(1) == 0 ? "male" : "female", age = 0, culture = father.culture, family_name = father.family_name)
    raise "Invalid culture" unless culture
    raise "Invalid family name" unless family_name
    
    # create a new character with the given parents
    character = Character.new
    character.sex = sex
    character.father = father.id unless father.nil?
    character.mother = mother.id unless mother.nil?
    # culture and names are inherited from father
    character.culture_id = culture.id
    
    character.christian_name = culture.generate_christian_name(character.sex)
    character.family_name = family_name
    
    # born this current date (adjusted for age) and is of course single
    character.dob = GameConfig.current_date - ((365 * age) + (age > 0 ? rand(365) : 0))
    
    # initialise characters attributes
    if character.age > 0
      character.prestige = 0 + rand(character.age * 20)
      character.piety = 0 + rand(character.age * 2)
      character.wealth = 0 + rand(character.age * 100)
    end

    # generate characters skills
    min_diplomacy = 0
    max_diplomacy = 0
    min_martial = 0
    max_martial = 0
    min_stewardship = 0
    max_stewardship = 0
    min_intrigue =  0
    max_intrigue = 0
    if father
      min_diplomacy += (father.diplomacy > 10 ? 1 : 0)
      max_diplomacy += (father.diplomacy / 2) + (father.diplomacy > 10 ? 1 : 0)
      min_martial += (father.martial / 4) + (father.martial > 10 ? 1 : 0)
      max_martial += (father.diplomacy  / 2) + (father.martial / 4) + (father.martial > 10 ? 1 : 0)
      min_stewardship += (father.stewardship > 10 ? 1 : 0)
      max_stewardship += (father.stewardship / 2) + (father.stewardship > 10 ? 1 : 0)
      min_intrigue +=  (father.intrigue > 10 ? 1 : 0)
      max_intrigue += (father.intrigue / 2) + (father.intrigue > 10 ? 1 : 0)
    else # assume father had 8 in all skills
      min_diplomacy += 0
      max_diplomacy += 4
      min_martial += 2
      max_martial += 6
      min_stewardship += 0
      max_stewardship += 4
      min_intrigue +=  0
      max_intrigue += 4
    end  
    if mother
      min_diplomacy += (mother.diplomacy / 4) + (mother.diplomacy > 10 ? 1 : 0)
      max_diplomacy += ( (mother.diplomacy / 2) / 2) + (mother.diplomacy / 4) + (mother.diplomacy > 10 ? 1 : 0)
      min_martial += (mother.martial > 10 ? 1 : 0)
      max_martial += ( (mother.diplomacy / 2) / 2) + (mother.martial > 10  ? 1 : 0)
      min_stewardship += (mother.stewardship > 10  ? 1 : 0)
      max_stewardship += ( (mother.stewardship / 2) / 2) + (mother.stewardship > 10  ? 1 : 0)
      min_intrigue +=  (mother.intrigue > 10  ? 1 : 0)
      max_intrigue += ( (mother.intrigue / 2) / 2) + (mother.intrigue > 10  ? 1 : 0)
    else # assume mother had 8 in all skills
      min_diplomacy += 2
      max_diplomacy += 4
      min_martial += 0
      max_martial += 2
      min_stewardship += 0
      max_stewardship += 2
      min_intrigue +=  0
      max_intrigue += 2
    end  
   
    character.diplomacy = rand(max_diplomacy - min_diplomacy) + min_diplomacy + (character.adult? ? rand(6) : 0)
    character.martial = rand(max_martial - min_martial) + min_martial + (character.adult? ? rand(6) : 0)
    character.stewardship = rand(max_stewardship - min_stewardship) + min_stewardship + (character.adult? ? rand(6) : 0)
    character.intrigue = rand(max_intrigue - min_intrigue) + min_intrigue + (character.adult? ? rand(6) : 0)
    
    if !character.save
      return nil
    end
    
    return character
  end
  
  def initialize (params = nil)
    super params
    self.marital_status = "single"
    self.dob = GameConfig.current_date
    self.honour = 20
    self.prestige = 0
    self.piety = 0
    self.wealth = 0
    self.loyalty = 100
    self.fertility = 0
    self.health = 20
    self.diplomacy = 0
    self.martial = 0
    self.stewardship = 0
    self.intrigue = 0
  end
  
  def controlled_by?(character)
    return false unless character
    controlled_by.id == character.id
  end
  
  def controlled_by
    if self.ruler? 
      return self
    end
    liege = self.liege
    return liege unless liege.nil? or !(liege.family?(self))
    return self
  end
  
  def sub_gold(amount)
    self.wealth -= amount
    self.save
  end
  
  def add_gold(amount)
    self.wealth += amount
    self.save
  end
  
  def has_gold?(amount)
    self.wealth >= amount
  end
  
  def king? (kingdom = nil)
    has_title? 1, 6, kingdom
  end
  
  def has_title?(category, sub_category, territory = nil)
    if territory
      titles = self.titles.find_all_by_category_and_sub_category_and_territory(category, sub_category, territory)
    else
      titles = self.titles.find_all_by_category_and_sub_category(category, sub_category)
    end
    titles && titles.size > 0
  end
  
  def belongs_to_court (territory)
    return false unless territory
    court = self.court_territory
    court.id == territory.id unless court.nil? 
  end
  
  def court_territory
    # the territory of their liege unless they rule a territory
    kingdom = Kingdom.with_ruler self
    return kingdom unless kingdom.nil?
    duchy = Duchy.with_ruler self
    return duchy unless duchy.nil?
    county = County.with_ruler self
    return county unless county.nil?
    town = Town.with_ruler self
    return town unless town.nil?
    ruler = self.liege
    return ruler.court_territory unless ruler.nil?
  end
  
  def court_ruler
    territory = self.court_territory
    return court_territory.ruler unless territory.nil?
  end
  
  def ruler? (court = nil)
    court = self.court_territory unless court
    ruler = court.ruler unless court.nil?
    return false unless ruler
    return (court.ruler.id == self.id)
  end
  
  def die
    list = self.successors
    successor = list[0] unless list.nil?
    successor.inherit self unless successor.nil?
    self.health = 0
    self.wealth = 0
    ch = self.partner
    if ch
      ch.marital_status = "widowed"
      ch.partner_id = 0
      ch.save
    end
    self.save
  end
  
  def inherit (character)
    self.wealth += character.wealth
    Title.transfer_feudal_titles character, self
    Title.revoke_ecclesiastical_titles character
    Title.revoke_court_titles character
    Relation.transfer_claims(character, self)
    Relation.transfer_vassals(character, self)
    Relation.transfer_truces(character, self)
    Relation.transfer_wars(character, self)
  end
  
  def compare (character)
    title1 = self.highest_title
    title2 = character.highest_title
    if title1 && title1.greater_rank_than?(title2)
      return -1
    end
    if title2 && title2.greater_rank_than?(title1)
      return 1
    end
    if self.prestige > character.prestige
      return -1
    end
    if character.prestige > self.prestige
      return 1
    end
    return 0
  end
  
  def name_and_title
    n = self.name
    t = self.highest_title_name
    n += ", " + t unless t.nil?
    return n
  end
  
  def giveable_titles
    giveable = Array.new
    for title in self.titles
      giveable = giveable << title unless !title.feudal? or title.lord? or title.knight? or title.emperor?
    end
    return giveable
  end
  
  def highest_title_name
    title = self.highest_title
    return title.name unless title.nil?
  end
  
  def highest_title
    if self.titles.nil?
      return nil
    end
    @highest_title = nil
    self.titles.each do |title|
      if @highest_title.nil? || title.greater_rank_than? (@highest_title)
        @highest_title = title
      end
    end
    return @highest_title
  end
  
  def marriable? (proposer_character)
    return false unless proposer_character
    return false unless self.marital_status != "married"
    potentials = Character.find_unmarried_for(self, proposer_character)
    return potentials && potentials.size > 0
  end
  
  def alive?
    self.health > 0
  end
  
  def npc?
    !(self.player)
  end
  
  def clergy?
    title = highest_title
    return false unless title
    title.ecclesiastical?
  end
  
  def attribute_modifiers (character_attribute)
    total = 0
    if character_traits
      character_traits.each do |trait|
        if trait.character_attribute == character_attribute
          total += trait.modifier
        end
      end
    end
    return total
  end
  
  def adjusted_martial_skill
    self.martial + attribute_modifiers("Martial")
  end
  
  def adjusted_diplomacy_skill
    self.diplomacy + attribute_modifiers("Diplomacy")
  end
  
  def adjusted_stewardship_skill
    self.stewardship + attribute_modifiers("Stewardship")
  end
  
  def adjusted_intrigue_skill
    self.intrigue + attribute_modifiers("Intrigue")
  end
  
  def adjusted_fertility
    self.fertility + attribute_modifiers("Fertility")
  end
  
  def adjusted_health
    self.health + attribute_modifiers("Health")
  end
  
  def yearly_piety_bonus
    attribute_modifiers("Piety")
  end
  
  def yearly_prestige_bonus
    attribute_modifiers("Prestige")
  end
  
  def special_case? (special_case)
    if character_traits
      character_traits.each do |trait|
        if trait.special_case? special_case
          return true
        end
      end
    end
    return false
  end
  
  def family_members
    Character.find_by_family_name(self.family_name)
  end
  
  def nephews(alive_only)
    sibs = self.siblings
    return nil unless sibs
    list = Array.new
    for ch in sibs
      nephews = ch.children("male",true)
      list = list + nephews unless nephews.nil?
    end
    return list
  end
  
  def paternal_uncles(alive_only=true)
    father = get_father
    uncles = father.siblings("male",alive_only) unless father.nil?
    return uncles
  end
  
  def maternal_uncles(alive_only=true)
    mother = get_mother
    uncles = mother.siblings("male",alive_only) unless mother.nil?
    return uncles
  end
  
  def successors
   list = Array.new
    # todo those elevated by church law to first in succession
    # sons
    male_children = self.children("male",true)
    list = list + male_children unless male_children.nil?
    # brothers
    brothers = self.siblings("male",true)
    list = list + brothers unless brothers.nil?
    # father
    father = self.get_father
    list = list << father unless father.nil? or !father.alive?
    # nephews
    nephews = self.nephews(true)
    list = list + nephews unless nephews.nil?
    # paternal uncles
    uncles = self.paternal_uncles(true)
    list = list + uncles unless uncles.nil?
    # daughters
    female_children = self.children("female",true)
    list = list + female_children unless female_children.nil?
    # liege
    list = list << self.liege unless self.liege.nil?
    # spouse
    if self.married?
      list = list << self.partner unless self.partner.nil? or !self.partner.alive?
    end
    # mother
    mother = self.get_mother
    list = list << mother unless mother.nil? or !mother.alive?
    # maternal uncles
    uncles = self.maternal_uncles (true)
    list = list + uncles unless uncles.nil?
    
    # now make sure list is unique
    hash = Hash.new
    ret = Array.new
    for ch in list
      ret = ret << ch unless hash[ch.id]
      hash[ch.id] = ch
    end
    return ret
  end
  
  def partner
    return nil unless self.partner_id && self.partner_id > 0
    Character.find(self.partner_id)
  end
  
  def betroth (character)
    self.marital_status = "betrothed"
    character.marital_status = "betrothed"
    self.partner_id = character.id
    character.partner_id = self.id
    self.save
    character.save
  end
  
  def marry (character)
    self.marital_status = "married"
    character.marital_status = "married"
    self.partner_id = character.id
    character.partner_id = self.id
    character.family_name = self.family_name unless character.male?
    self.family_name = character.family_name unless self.male?
    self.save
    character.save
    # the female partner becomes a vassal of the male partner or their liege upon marriage
    female = self.male? ? character : self
    male = self.male? ? self : character
    liege = male.vassal? ? male.liege : male
    Relation.vassalize female, liege
  end
  
  def family? (character)
    if self.family_name == character.family_name
      return true
    end
  end
  
  def sibling? (character)
    self.father == character.father or self.mother == character.mother
  end
  
  def kin? (character)
    # two characters are said to be kin if they share a common ancestor in the last 3 generations
    check_lineage character
  end
  
  def change_family_name (new_name)
    if self.wealth < 250
      return false
    end
    self.prestige /= 2
    adjust_vassals_loyalty -50, true
    adjust_vassals_loyalty -25
    self.wealth -= 250
    self.family_name = new_name
  end
  
  def honour_description
    if self.honour <= -20
      "Dishonourable Scum"
    elsif self.honour <= -10
      "Villain"
    elsif self.honour <= 0
      "Tarnished"
    elsif self.honour <= 10
      "Slightly Tarnished"
    elsif self.honour < 20
      "Respectable"
    else
      "Honourable"
    end
  end
  
  def health_description
    if self.health <= 0
      "Dead"
    elsif self.health <= 5
      "Hovering at Death's door"
    elsif self.health <= 10
      "Bed-ridden"
    elsif self.health <= 15
      "Unwell"
    elsif self.health < 20
      "Fit"
    else
      "Healthy"
    end
  end
  
  def adjust_vassals_loyalty (modifier, family_only = false)
    vassals.each do |v|
      v.loyalty -= modifer unless family_only && !v.family?(self)
      v.save
    end
  end
  
  def set_father (character)
    if character
      @father = character.id
    else
      @father = 0
    end
  end
  
  def get_father
    Character.find(self.father) unless self.father.nil?
  end
  
  def set_mother (character)
    if character
      @mother = mother.id
    else
      @mother = 0
    end
  end
  
  def get_mother
    Character.find(self.mother) unless self.mother.nil?
  end
  
  def name
    "#{self.christian_name} #{self.family_name}"
  end
  
  def opposite_sex
    self.male? ? "female" : "male"
  end
  
  def male?
    self.sex == "male"
  end
  
  def female?
    self.sex == "female"
  end
  
  def student?
    !self.adult? && self.age > 6
  end
  
  def married?
    self.marital_status == "married"
  end
  
  def single?
    self.marital_status == "single"
  end
  
  def betrothed?
    self.marital_status == "betrothed"
  end
  
  def widowed?
    self.marital_status == "widowed"
  end
  
  def adult?
    self.age >= 16
  end
  
  def age
    return nil unless self.dob
    (GameConfig.current_date - self.dob) / 365
  end
  
  def children (filter_sex = nil, filter_legitimate = true, filter_alive=false)
    list = nil
    if self.sex == "male"
      if filter_sex.nil?
        list = Character.find_all_by_father(self.id, :order => "dob asc")
      else
        list = Character.find_all_by_father_and_sex(self.id, filter_sex, :order => "dob asc")
      end
    else
      if filter_sex.nil?
        list = Character.find_all_by_mother(self.id, :order => "dob asc")
      else
        list = Character.find_all_by_mother_and_sex(self.id, filter_sex, :order => "dob asc")
      end
    end
    if list && filter_legitimate
      list2 = Array.new
      list.each do |c|
        list2 = list2 << c unless c.special_case?("Bastard")
      end
      list = list2
    end
    return list
  end
  
  def siblings (filter_sex = nil, alive_only = false)
    return nil unless (self.father or self.mother)
    sql_select = Character.sql_select_from_character
    sql_where = " WHERE c.id <> #{self.id} "
    if self.father
      sql_where += " AND c.father = #{self.father} "
    end
    if self.mother
      sql_where += " AND c.mother = #{self.mother}"
    end
    if sex
      sql_where += " AND " + Character.sql_clause_sex (sex)
    end
    if alive_only
      sql_where += " AND " + Character.sql_clause_alive_only
    end
    Character.find_by_sql(sql_select + sql_where + Character.sql_order_by_oldest)
  end
  
  def liege
    rel = Relation.find_liege self
    return rel.to_character unless rel.nil?
  end
  
  def vassals
    Character.convert_relations_from_characters(Relation.find_vassals(self))
  end
  
  def vassal? (character=nil)
    liege = self.liege
    return !(liege.nil?) unless character
    !liege.nil? && liege.id == character.id
  end
  
  def allies
    Character.convert_relations_to_characters(Relation.find_allies(self))
  end
  
  def allied? (character)
    Relation.allies? self, character
  end
  
  def war? (character)
    Relation.war? self, character
  end
  
  def truce? (character)
    Relation.truce? self, character
  end
  
  def peace? (character)
    Relation.peace? self, character
  end
  
  def claims
    Character.convert_relations_titles(Relation.find_claims(self))
  end
  
  def wars
    Character.convert_relations_to_characters(Relation.find_wars(self))
  end
  
  def truces
    Character.convert_relations_to_characters(Relation.find_truces(self))
  end
  
  protected
  def Character.convert_relations_titles (relations)
    return relations unless relations && relations.instance_of? (Array)
    list = nil
    if relations
      list = Array.new
      for rel in relations
        list = list << rel.title
      end
    end
    return list
  end
  
  def Character.convert_relations_from_characters (relations)
    return relations unless relations && relations.instance_of? (Array)
    list = nil
    if relations
      list = Array.new
      for rel in relations
        list = list << rel.character
      end
    end
    return list
  end
  
  def Character.convert_relations_to_characters (relations)
    return relations unless relations && relations.instance_of? (Array)
    list = nil
    if relations
      list = Array.new
      for rel in relations
        list = list << rel.to_character
      end
    end
    return list
  end
  
  def Character.sql_select_from_character
  "SELECT DISTINCT c.id" +
                  ", c.player_id" +
                  ", c.partner_id" +
                  ", c.father" +
                  ", c.mother" +
                  ", c.christian_name" +
                  ", c.family_name" +
                  ", c.culture_id" +
                  ", c.honour" +
                  ", c.prestige" +
                  ", c.piety" +
                  ", c.wealth" +
                  ", c.sex" +
                  ", c.dob" +
                  ", c.marital_status" +
                  ", c.diplomacy" +
                  ", c.martial" +
                  ", c.stewardship" +
                  ", c.intrigue" +
                  ", c.loyalty" +
                  ", c.fertility" +
                  ", c.health" +
                  ", c.created_at" +
                  ", c.updated_at" +
                  " FROM characters AS c "  
  end
  
  def Character.sql_order_by_oldest
    " ORDER BY c.dob ASC"
  end
  
  def Character.sql_clause_min_age (min_age)
    "c.dob <= " + (GameConfig.current_date - (365 * min_age)).to_s
  end
  
  def Character.sql_clause_npc_only
    "(c.player_id IS NULL OR c.player_id = 0 )"
  end
  
  def Character.sql_clause_marital_status_excluded (status)
    "c.marital_status <> '#{status}'"
  end
  
  def Character.sql_clause_family_name (name)
    "c.family_name = '#{name}'"
  end
  
  def Character.sql_clause_exclude_family_name (name)
    "c.family_name <> '#{name}'"
  end
  
  def Character.sql_clause_exclude_clergy
    "c.id NOT IN (SELECT character_id FROM titles WHERE category = 2 AND sub_category > 1)"
  end
  
  def Character.sql_clause_alive_only
    "c.health > 0"
  end
  
  def Character.sql_clause_sex (sex)
    "c.sex ='#{sex}'"
  end
  
  def Character.sql_clause_title (title_category, title_sub_category)
    "c.id IN (SELECT character_id FROM titles WHERE category = #{title_category} AND sub_category = #{title_sub_category})"
  end
  
  def Character.sql_clause_vassal_of(liege)
    "c.id IN (SELECT character_id FROM relations WHERE category = 1 AND title_id IN (SELECT id FROM titles WHERE character_id = #{liege.id}))"
  end
  
  def Character.sql_clause_realm(territory, exclude_ruler = false)
    sub_cat = 0
      if territory.instance_of? Kingdom
        sub_cat = 6
      elsif territory.instance_of? Duchy
        sub_cat = 5
      elsif territory.instance_of? County
        sub_cat = 4
      elsif territory.instance_of? Town
        sub_cat = 3
      end
      sql_has_title = "SELECT character_id FROM titles WHERE category = 1 AND sub_category = #{sub_cat} AND territory = #{territory.id}"
      
      sql_title_holders_titles = "SELECT id FROM titles WHERE character_id IN (#{sql_has_title})"
      sql_vassal_of_title_holder = "SELECT character_id FROM relations WHERE category = 1 AND title_id IN (#{sql_title_holders_titles})"
      
      sql_doesnt_have_court = "SELECT character_id FROM titles WHERE category = 1 AND sub_category > 2"
      sql_include_ruler = ""
      sql_include_ruler = "c.id IN (#{sql_has_title}) OR " unless exclude_ruler
      "#{sql_include_ruler}((c.id NOT IN (#{sql_doesnt_have_court}) AND c.id IN (#{sql_vassal_of_title_holder}) ))"
  end
  
  def check_lineage (ch1 , ch2 = self, generation_self = 0, generation_other = 0, max_generations = 3)
    if ch1.nil? or ch2.nil?
      return false
    end
    while generation_self <= max_generations do
      while generation_other  <= max_generations do
        if ch1.sibling? ch2
          return true
        end
        if check_lineage ch1.get_father, ch2, generation_self
          return true
        end
        if check_lineage ch1.get_mother, ch2, generation_self
          return true
        end
        generation_other += 1
      end
      if check_lineage ch1, ch2.get_father, generation_self
        return true
      end
      if check_lineage ch1, ch2.get_mother, generation_self
        return true
      end
      generation_self += 1
    end
    return false
  end
end
